----------------------------------------------
-- @name: index_skip_scan
-- @author: dion cho
-- @descripiotn: test for index skip scan
----------------------------------------------

-- create objects
drop table t1 purge;
create table t1(c1 char(2), c2 int, c3 char(100));
create index t1_idx on t1(c1, c2);

-- generate low distinct cardinality of c1
insert into t1
select 'A', level, 'dummy'
from dual
connect by level <= 5000
;

insert into t1
select 'B', level, 'dummy'
from dual
connect by level <= 5000
;

commit;

@gather t1

---------------------------------------------------------
-- case1: analyze the cost increase as the scan range increases
-- when 1
explain plan for
select /*+ index_ss(t1) */ *
from t1
where c2 between 1 and 1
;

@plan

-- when 10
explain plan for
select /*+ index_ss(t1) */ *
from t1
where c2 between 1 and 10
;

@plan

-- when 100
explain plan for
select /*+ index_ss(t1) */ *
from t1
where c2 between 1 and 100
;

@plan

-- when 1000
explain plan for
select /*+ index_ss(t1) */ *
from t1
where c2 between 1 and 1000
;

@plan


-------------------------------------------------------
-- case2: compare the workload
set arraysize 1

-- when 1

select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 = 1
;

@stat


-- when 10
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 10
;

@stat


-- compare with this
select /*+ gather_plan_statistics index(t1) */ 
  *
from t1
where c1 = 'A' and c2 between 1 and 10
union all
select /*+ gather_plan_statistics index(t1) */ 
  *
from t1
where c1 = 'B' and c2 between 1 and 10
;

@stat


-- when 100
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 100
;

@stat

-- compare with this
select /*+ gather_plan_statistics index(t1) */ 
  *
from t1
where c1 = 'A' and c2 between 1 and 100
union all
select /*+ gather_plan_statistics index(t1) */ 
  *
from t1
where c1 = 'B' and c2 between 1 and 100
;

@stat


-- when 1000
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 1000
;

@stat

-- compare with this
select /*+ gather_plan_statistics index(t1) */ 
  *
from t1
where c1 = 'A' and c2 between 1 and 1000
union all
select /*+ gather_plan_statistics index(t1) */ 
  *
from t1
where c1 = 'B' and c2 between 1 and 1000
;

@stat

 

-----------------------------------------------------------------
-- see that default fetch size is considered "2" in this case
set arraysize 1
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 100
;

@stat


set arraysize 2
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 100
;

@stat

set arraysize 4
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 100
;

@stat

set arraysize 8
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 100
;

@stat


----------------------------------------------------------------
-- case3: when skip scan is considered to be efficient
-- low distinct cardinality

set arraysize 15

drop table t1 purge;
create table t1(c1 char(2), c2 int, c3 char(100));
create index t1_idx on t1(c1, c2);

insert into t1
select 'A', level, 'dummy'
from dual
connect by level <= 5000;

insert into t1
select 'B', level, 'dummy'
from dual
connect by level <= 5000;

commit;

@gather t1

explain plan for
select *
from t1
where c2 between 1 and 50;

@plan


select /*+ gather_plan_statistics index_ss(t1) */ *
from t1
where c2 between 1 and 50;

@stat

select /*+ gather_plan_statistics full(t1) */ *
from t1
where c2 between 1 and 50;

@stat

select /*+ gather_plan_statistics index(t1) */ *
from t1
where c1 = 'A' and c2 between 1 and 50
union all
select /*+ gather_plan_statistics index(t1) */ *
from t1
where c1 = 'B' and c2 between 1 and 50
;

@stat


---------------------------------------------------------------
-- case4: when table full scan is considered to be efficient
-- a little bit high distinct cardinality
truncate table t1;

insert into t1
select s.value, rownum, 'dummy'
from 
  (select rpad((level-1)||'',2,'0') as value from dual connect by level <= 100) s,
  (select level from dual connect by level <= 50) t
;


insert into t1
select s.value, rownum, 'dummy'
from 
  (select rpad((level-1)||'',2,'0') as value from dual connect by level <= 100) s,
  (select level from dual connect by level <= 50) t
;


@gather t1


explain plan for
select *
from t1
where c2 between 1 and 50
;

@plan


select /*+ gather_plan_statistics index_ss(t1) */ *
from t1
where c2 between 1 and 50
;

@stat

select /*+ gather_plan_statistics full(t1) */ *
from t1
where c2 between 1 and 50
;

@stat

select /*+ gather_plan_statistics index(t1) */ *
from t1
where c1 between '00' and '99'
      and c2 between 1 and 50
;

@stat


@stat

 

-------------------------------------
-- Appendix: Can we trace index skip scan?
@capture_on

set arraysize 1
@trace_on 10200 1
select /*+ gather_plan_statistics index_ss(t1) */ 
  *
from t1
where c2 between 1 and 100
;

@stat

@trace_off
exec tree_dump('t1_idx');
@trace_file
ho start &_editor &trace_file

@capture_off
